package application;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import utils.categories.Foursquare2YP;
import utils.config.Config;


public class BusinessRanking {
	
	public String IN_DB;
	public double [][] AREA_BBOX;
	
	public static void main(String[] args) throws Exception {
		
		BusinessRanking ec = new BusinessRanking();
		ec.read();

	}
	
	public BusinessRanking() {
		
		Config config = new Config(this.getClass().getSimpleName()+".ser"); 
		IN_DB = (String)config.get("IN_DB");
		AREA_BBOX = (double[][])config.get("AREA_BBOX");
	}
	
	public void read() throws Exception {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
	    Connection c = DriverManager.getConnection(IN_DB);
	    Statement s = c.createStatement();	
	    
	    double[][] bbox = AREA_BBOX;
	    double minlon = bbox[0][0];
	    double minlat = bbox[0][1];
	    double maxlon = bbox[1][0];
	    double maxlat = bbox[1][1];
	    
	    String points = "'MULTIPOINT("+minlon+" "+minlat+","+maxlon+" "+minlat+","+maxlon+" "+maxlat+","+minlon+" "+maxlat+")'";
		
	    Foursquare2YP fcat = new Foursquare2YP();
		Map<String,List<String>> categories = fcat.CATEGORIES;
		
		//for statistics computation
		double total = 0;
		double [] tot_categories = new double[categories.size()];
		
		int counter = 0;
		for(String category: categories.keySet()){
			double count = 0; 
			System.out.println();
			
			String query = "SELECT idBusiness,name,address,city,state,category,AsText(coord) AS coord FROM Business WHERE (Intersects(coord,GeomFromText("+points+"))) AND category IN ("+getCategoryCondition(categories.get(category))+");";
		    ResultSet rs = s.executeQuery(query);
		    
		    while(rs.next()) {
		    	int idBusiness = rs.getInt("idBusiness");
		    	String name = rs.getString("name");
		    	String address = rs.getString("address");
		    	String city = rs.getString("city");
		    	String state = rs.getString("state");
		    	String cat = rs.getString("category");
		    	String coord = rs.getString("coord");
		    	
		    	System.out.println(idBusiness+": "+name+", "+address+", "+city+", "+cat+", ("+coord+")");
		    	
		    	
		    	count++;
		    	total++;
		    }
		    
	    	System.out.println();
	    	System.out.println(count);
		    
		    tot_categories[counter] = count;
		    counter++;
		}
		
		System.out.println();
		for(int i=0;i<tot_categories.length;i++)
			System.out.println((tot_categories[i]/total)*100);
		
		System.out.println();
		System.out.println(total);
	    
	    

	    
	    s.close();
	    c.close();
	    System.out.println("Done");
	    
	}
	
	public String getCategoryCondition(List<String> elements){

		String queryCategory = "";
		
		for(String el: elements)
			queryCategory += "'"+el+"',";
		
		
		return queryCategory.substring(0,queryCategory.length()-1);
	}

}
